﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace Functions
{
    public static class Database
    {
        public static string ConStr = "";
        static SqlConnection Con { get; set; }
        //public static Database()
        //{
        //}
        public static void setConnect(string str)
        {
            Con = new SqlConnection();
            ConStr = str;
            Con.ConnectionString = str;
        }
        public static void Open()
        {
            try
            {
                if (Con != null)
                {
                    Con = new SqlConnection();
                    Con.ConnectionString = ConStr;
                }
                if (Con.State != ConnectionState.Open)
                    Con.Open();
            }
            catch (Exception e) { throw e; }
        }
        public static void Close()
        {
            try { if (Con != null) if (Con.State != ConnectionState.Closed) Con.Close(); }
            catch (Exception e) { throw e; }
        }
        public static DataTable returnDataTable(string query)
        {
            try
            {
                Open();
                SqlCommand cmd = new SqlCommand(query, Con);
                SqlDataReader dr = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(dr);
                Close();
                return dt;
            }
            catch (Exception ex) { Console.WriteLine(ex.Message); return null; }
        }
        public static int ExecuteInsert(Hashtable _myHashtable, string table)
        {
            try
            {
                string keyString = null;
                string keyString2 = null;
                string querySQL = null;
                foreach (DictionaryEntry Item in _myHashtable)
                {
                    keyString += "[" + Item.Key.ToString() + "],";
                    keyString2 += "@" + Item.Key.ToString() + ",";
                }
                keyString = keyString.Substring(0, keyString.Length - 1);
                keyString2 = keyString2.Substring(0, keyString2.Length - 1);
                querySQL = "INSERT INTO " + table + "(" + keyString + ") values (" + keyString2 + ") ";
                SqlCommand cmd = new SqlCommand(querySQL, Con);
                foreach (DictionaryEntry Item in _myHashtable)
                {
                    cmd.Parameters.AddWithValue("@" + Item.Key.ToString(), Item.Value.ToString());
                }
                Open();
                int y = cmd.ExecuteNonQuery();
                Close();
                return y;
            }
            catch (Exception ex) { Console.WriteLine(ex.Message); return 0; }
        }

        public static int ExecuteUpdate(Hashtable myHashtable, string _table, string RowName, string RowValue)
        {
            try
            {
                string keyString = null;
                int y = 0;

                foreach (DictionaryEntry Item in myHashtable)
                {
                    keyString += "[" + Item.Key.ToString() + "] = @" + Item.Key.ToString() + ",";
                }
                keyString = keyString.Substring(0, keyString.Length - 1);
                string query = "UPDATE [" + _table + "] SET " + keyString + " WHERE [" + RowName + "] = @" + RowName;
                SqlCommand cmd = new SqlCommand(query, Con);
                foreach (DictionaryEntry Item in myHashtable)
                {
                    cmd.Parameters.AddWithValue("@" + Item.Key.ToString(), Item.Value.ToString());
                }
                cmd.Parameters.AddWithValue("@" + RowName, RowValue);
                Open();
                y = cmd.ExecuteNonQuery();
                Close();
                return y;
            }
            catch (Exception ex) { Console.WriteLine(ex.Message); return 0; }
        }

        public static string ExecuteGetLastID(string _table, string colID)
        {
            string query = "SELECT IDENT_CURRENT('" + _table + "') AS " + colID;
            DataTable dt = returnDataTable(query);
            if (dt != null) return dt.Rows[0][colID].ToString();
            else return null;
        }
        public static int ExecuteDeleteViaField(Hashtable hash, string colID)
        {
            try
            {
                Open();
                int y = 0;
                foreach (DictionaryEntry item in hash)
                {
                    string query = string.Format("DELETE FROM [{0}] WHERE {1} = '{2}'", item.Key, colID, item.Value);
                    SqlCommand cmd = new SqlCommand(query, Con);
                    y = cmd.ExecuteNonQuery();
                }
                Close();
                return y;
            }
            catch (Exception ex) { Console.WriteLine(ex.Message); return 0; }
        }
        public static int ExecuteDeleteViaCustom(Hashtable hash)
        {
            try
            {
                Open();
                int y = 0;
                foreach (DictionaryEntry item in hash)
                {
                    string query = "DELETE FROM [" + item.Key + "] WHERE " + item.Value;
                    SqlCommand cmd = new SqlCommand(query, Con);
                    y = cmd.ExecuteNonQuery();
                }
                Close();
                return y;
            }
            catch (Exception ex) { Console.WriteLine(ex.Message); return 0; }
        }
        public static string prameter(Hashtable hash)
        {
            string result = "";
            foreach (DictionaryEntry item in hash)
            {
                string[] data = null;
                data = item.Value as string[];
                string sym = " = ";
                string val = "''";
                if (data != null && data.Length == 2)
                {
                    sym = data[0];
                    val = data[1];
                }
                else
                {
                    val = "'" + item.Value + "'";
                }
                result += " " + item.Key + sym + val + " AND";
            }
            if (result.Length > 0) result = result.Substring(0, result.Length - 3);
            result = " WHERE" + result;
            return result;
        }
    }
}
